<!DOCTYPE html>
<html >
<head>

    <!--[if lt IE 9]>
        <style>body {display: none; background: none !important} </style>
        <meta http-equiv="Refresh" Content="0; url=//outdatedbrowser.com/" />
    <![endif]-->

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
<meta name="format-detection" content="telephone=no" />
<meta name="author" content="xiaoyou" />



<meta name="description" content="对于SQL的Join，在学习起来可能是比较乱的。我们知道，SQL的Join语法有很多inner的，有outer的，有left的，有时候，对于Select出来的结果集是什么样子有点不是很清楚。Coding Horror上有一篇文章（实在不清楚为什么Coding Horror也被墙）通过 文氏图 Venn diagrams解释了SQL的Join。">
<meta name="keywords" content="MySql">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql的七种join">
<meta property="og:url" content="http://yoursite.com/2017/04/04/mysql-7-join/index.html">
<meta property="og:site_name" content="Lost Youth">
<meta property="og:description" content="对于SQL的Join，在学习起来可能是比较乱的。我们知道，SQL的Join语法有很多inner的，有outer的，有left的，有时候，对于Select出来的结果集是什么样子有点不是很清楚。Coding Horror上有一篇文章（实在不清楚为什么Coding Horror也被墙）通过 文氏图 Venn diagrams解释了SQL的Join。">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671067372.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671283981.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671404628.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671491566.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671586536.jpg">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671715548.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671817769.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671960016.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906672122367.jpg">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906672303744.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906672393192.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906672485581.png">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/14906672571414.jpg">
<meta property="og:image" content="http://oo77gy3uq.bkt.clouddn.com/SouthEast.">
<meta property="og:updated_time" content="2017-04-26T06:06:45.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql的七种join">
<meta name="twitter:description" content="对于SQL的Join，在学习起来可能是比较乱的。我们知道，SQL的Join语法有很多inner的，有outer的，有left的，有时候，对于Select出来的结果集是什么样子有点不是很清楚。Coding Horror上有一篇文章（实在不清楚为什么Coding Horror也被墙）通过 文氏图 Venn diagrams解释了SQL的Join。">
<meta name="twitter:image" content="http://oo77gy3uq.bkt.clouddn.com/14906671067372.png">

<link rel="apple-touch-icon" href= "/apple-touch-icon.png">


    <link rel="alternate" href="/atom.xml" title="Lost Youth" type="application/atom+xml">



    <link rel="shortcut icon" href="/favicon.png">



    <link href="//cdn.bootcss.com/animate.css/3.5.1/animate.min.css" rel="stylesheet">



    <link href="//cdn.bootcss.com/fancybox/2.1.5/jquery.fancybox.min.css" rel="stylesheet">



    <script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
    <link href="//cdn.bootcss.com/pace/1.0.2/themes/blue/pace-theme-minimal.css" rel="stylesheet">


<link rel="stylesheet" href="/css/style.css">



<link href="//cdn.bootcss.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet">


<title>Mysql的七种join | Lost Youth</title>

<script src="//cdn.bootcss.com/jquery/2.2.4/jquery.min.js"></script>
<script src="//cdn.bootcss.com/clipboard.js/1.5.10/clipboard.min.js"></script>

<script>
    var yiliaConfig = {
        fancybox: true,
        animate: true,
        isHome: false,
        isPost: true,
        isArchive: false,
        isTag: false,
        isCategory: false,
        fancybox_js: "//cdn.bootcss.com/fancybox/2.1.5/jquery.fancybox.min.js",
        scrollreveal: "//cdn.bootcss.com/scrollReveal.js/3.1.4/scrollreveal.min.js",
        search: undefined
    }
</script>


    <script> yiliaConfig.jquery_ui = [false]; </script>



    <script> yiliaConfig.rootUrl = "\/";</script>






</head>
<body>
  <div id="container">
    <div class="left-col">
    <div class="overlay"></div>
<div class="intrude-less">
    <header id="header" class="inner">
        <a href="/" class="profilepic">
            <img src="/img/avatar.png" class="animated zoomIn">
        </a>
        <hgroup>
          <h1 class="header-author"><a href="/">xiaoyou</a></h1>
        </hgroup>

        

        


        
            <div id="switch-btn" class="switch-btn">
                <div class="icon">
                    <div class="icon-ctn">
                        <div class="icon-wrap icon-house" data-idx="0">
                            <div class="birdhouse"></div>
                            <div class="birdhouse_holes"></div>
                        </div>
                        <div class="icon-wrap icon-ribbon hide" data-idx="1">
                            <div class="ribbon"></div>
                        </div>
                        
                        <div class="icon-wrap icon-link hide" data-idx="2">
                            <div class="loopback_l"></div>
                            <div class="loopback_r"></div>
                        </div>
                        
                        
                        <div class="icon-wrap icon-me hide" data-idx="3">
                            <div class="user"></div>
                            <div class="shoulder"></div>
                        </div>
                        
                    </div>
                    
                </div>
                <div class="tips-box hide">
                    <div class="tips-arrow"></div>
                    <ul class="tips-inner">
                        <li>菜单</li>
                        <li>标签</li>
                        
                        <li>友情链接</li>
                        
                        
                        <li>关于我</li>
                        
                    </ul>
                </div>
            </div>
        

        <div id="switch-area" class="switch-area">
            <div class="switch-wrap">
                <section class="switch-part switch-part1">
                    <nav class="header-menu">
                        <ul>
                        
                            <li><a href="/">Home</a></li>
                        
                            <li><a href="/archives">Archives</a></li>
                        
                            <li><a href="/tools">Tools</a></li>
                        
                            <li><a href="/about">About</a></li>
                        
                        </ul>
                    </nav>
                    <nav class="header-nav">
                        <ul class="social">
                            
                                <a class="fa Email" href="mailto:123@123.com" title="Email"></a>
                            
                                <a class="fa GitHub" href="#" title="GitHub"></a>
                            
                                <a class="fa RSS" href="/atom.xml" title="RSS"></a>
                            
                        </ul>
                    </nav>
                </section>
                
                
                <section class="switch-part switch-part2">
                    <div class="widget tagcloud" id="js-tagcloud">
                        <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/Git/">Git</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Java8/">Java8</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/MySql/">MySql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/RocketMQ/">RocketMQ</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Spring-Boot/">Spring Boot</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Tomcat/">Tomcat</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/分布式-服务化/">分布式 服务化</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/唯一ID/">唯一ID</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/微服务/">微服务</a></li></ul>
                    </div>
                </section>
                
                
                
                <section class="switch-part switch-part3">
                    <div id="js-friends">
                    
                      <a class="main-nav-link switch-friends-link" href="https://hexo.io">Hexo</a>
                    
                      <a class="main-nav-link switch-friends-link" href="https://pages.github.com/">GitHub</a>
                    
                      <a class="main-nav-link switch-friends-link" href="http://moxfive.xyz/">MOxFIVE</a>
                    
                    </div>
                </section>
                

                
                
                <section class="switch-part switch-part4">
                
                    <div id="js-aboutme">Man</div>
                </section>
                
            </div>
        </div>
    </header>                
</div>
    </div>
    <div class="mid-col">
      <nav id="mobile-nav">
      <div class="overlay">
          <div class="slider-trigger"></div>
          <h1 class="header-author js-mobile-header hide"><a href="/" title="回到主页">xiaoyou</a></h1>
      </div>
    <div class="intrude-less">
        <header id="header" class="inner">
            <a href="/" class="profilepic">
                <img src="/img/avatar.png" class="animated zoomIn">
            </a>
            <hgroup>
              <h1 class="header-author"><a href="/" title="回到主页">xiaoyou</a></h1>
            </hgroup>
            
            <nav class="header-menu">
                <ul>
                
                    <li><a href="/">Home</a></li>
                
                    <li><a href="/archives">Archives</a></li>
                
                    <li><a href="/tools">Tools</a></li>
                
                    <li><a href="/about">About</a></li>
                
                <div class="clearfix"></div>
                </ul>
            </nav>
            <nav class="header-nav">
                        <ul class="social">
                            
                                <a class="fa Email" target="_blank" href="mailto:123@123.com" title="Email"></a>
                            
                                <a class="fa GitHub" target="_blank" href="#" title="GitHub"></a>
                            
                                <a class="fa RSS" target="_blank" href="/atom.xml" title="RSS"></a>
                            
                        </ul>
            </nav>
        </header>                
    </div>
    <link class="menu-list" tags="标签" friends="友情链接" about="关于我"/>
</nav>
      <div class="body-wrap"><article id="post-mysql-7-join" class="article article-type-post" itemscope itemprop="blogPost">
  
    <div class="article-meta">
      <a href="/2017/04/04/mysql-7-join/" class="article-date">
      <time datetime="2017-04-04T09:56:00.000Z" itemprop="datePublished">2017-04-04</time>
</a>


    </div>
  
  <div class="article-inner">
    
      <input type="hidden" class="isFancy" />
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      Mysql的七种join
    </h1>
  

      </header>
      
      <div class="article-info article-info-post">
        

        
    <div class="article-tag tagcloud">
        <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/MySql/">MySql</a></li></ul>
    </div>

        <div class="clearfix"></div>
      </div>
      
    
    <div class="article-entry" itemprop="articleBody">
      
          
        <h1 id="Mysql的七种join"><a href="#Mysql的七种join" class="headerlink" title="Mysql的七种join"></a>Mysql的七种join</h1><blockquote>
<p>对于SQL的Join，在学习起来可能是比较乱的。我们知道，SQL的Join语法有很多inner的，有outer的，有left的，有时候，对于Select出来的结果集是什么样子有点不是很清楚。Coding Horror上有一篇文章（实在不清楚为什么Coding Horror也被墙）通过 文氏图 Venn diagrams解释了SQL的Join。</p>
</blockquote>
<h1 id="建表"><a href="#建表" class="headerlink" title="建表"></a>建表</h1><p>在这里呢我们先来建立两张有外键关联的张表。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div></pre></td><td class="code"><pre><div class="line">CREATE DATABASE db0206;</div><div class="line">USE db0206;</div><div class="line"></div><div class="line">CREATE TABLE `db0206`.`tbl_dept`(  </div><div class="line">  `id` INT(11) NOT NULL AUTO_INCREMENT,</div><div class="line">  `deptName` VARCHAR(30),</div><div class="line">  `locAdd` VARCHAR(40),</div><div class="line">  PRIMARY KEY (`id`)</div><div class="line">) ENGINE=INNODB CHARSET=utf8;</div><div class="line"></div><div class="line">CREATE TABLE `db0206`.`tbl_emp`(  </div><div class="line">  `id` INT(11) NOT NULL AUTO_INCREMENT,</div><div class="line">  `name` VARCHAR(20),</div><div class="line">  `deptId` INT(11),</div><div class="line">  PRIMARY KEY (`id`),</div><div class="line">  FOREIGN KEY (`deptId`) REFERENCES `db0206`.`tb_dept`(`id`)</div><div class="line">) ENGINE=INNODB CHARSET=utf8;</div><div class="line">/*插入数据*/</div><div class="line">INSERT INTO tbl_dept(deptName,locAdd) VALUES(&apos;RD&apos;,11);</div><div class="line">INSERT INTO tbl_dept(deptName,locAdd) VALUES(&apos;HR&apos;,12);</div><div class="line">INSERT INTO tbl_dept(deptName,locAdd) VALUES(&apos;MK&apos;,13);</div><div class="line">INSERT INTO tbl_dept(deptName,locAdd) VALUES(&apos;MIS&apos;,14);</div><div class="line">INSERT INTO tbl_dept(deptName,locAdd) VALUES(&apos;FD&apos;,15);</div><div class="line"></div><div class="line">INSERT INTO tbl_emp(NAME,deptId) VALUES(&apos;z3&apos;,1);</div><div class="line">INSERT INTO tbl_emp(NAME,deptId) VALUES(&apos;z4&apos;,1);</div><div class="line">INSERT INTO tbl_emp(NAME,deptId) VALUES(&apos;z5&apos;,1);</div><div class="line"></div><div class="line">INSERT INTO tbl_emp(NAME,deptId) VALUES(&apos;w5&apos;,2);</div><div class="line">INSERT INTO tbl_emp(NAME,deptId) VALUES(&apos;w6&apos;,2);</div><div class="line"></div><div class="line">INSERT INTO tbl_emp(NAME,deptId) VALUES(&apos;s7&apos;,3);</div><div class="line"></div><div class="line">INSERT INTO tbl_emp(NAME,deptId) VALUES(&apos;s8&apos;,4);</div></pre></td></tr></table></figure>
<h1 id="文氏图与SQL语句的编写以及查询结果"><a href="#文氏图与SQL语句的编写以及查询结果" class="headerlink" title="文氏图与SQL语句的编写以及查询结果"></a>文氏图与SQL语句的编写以及查询结果</h1><h2 id="内连接"><a href="#内连接" class="headerlink" title="内连接"></a>内连接</h2><h3 id="内连接文氏图"><a href="#内连接文氏图" class="headerlink" title="内连接文氏图"></a>内连接文氏图</h3><p><img src="http://oo77gy3uq.bkt.clouddn.com/14906671067372.png" alt=""></p>
<h4 id="执行的sql语句以及执行的查询结果"><a href="#执行的sql语句以及执行的查询结果" class="headerlink" title="执行的sql语句以及执行的查询结果"></a>执行的sql语句以及执行的查询结果</h4><ul>
<li>执行的sql语句</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">select * from tbl_dept a inner join tbl_emp b on a.id=b.deptId;</div></pre></td></tr></table></figure>
<ul>
<li>查询结果<br>  <img src="http://oo77gy3uq.bkt.clouddn.com/14906671283981.png" alt=""></li>
</ul>
<h2 id="左外连接"><a href="#左外连接" class="headerlink" title="左外连接"></a>左外连接</h2><h3 id="左外连接文氏图"><a href="#左外连接文氏图" class="headerlink" title="左外连接文氏图"></a>左外连接文氏图</h3><p><img src="http://oo77gy3uq.bkt.clouddn.com/14906671404628.png" alt=""></p>
<h4 id="执行的sql语句以及执行的查询结果-1"><a href="#执行的sql语句以及执行的查询结果-1" class="headerlink" title="执行的sql语句以及执行的查询结果"></a>执行的sql语句以及执行的查询结果</h4><ul>
<li>执行的sql语句</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;</div></pre></td></tr></table></figure>
<ul>
<li>查询结果<br> <img src="http://oo77gy3uq.bkt.clouddn.com/14906671491566.png" alt=""></li>
</ul>
<h2 id="右外连接"><a href="#右外连接" class="headerlink" title="右外连接"></a>右外连接</h2><h3 id="右外连接文氏图"><a href="#右外连接文氏图" class="headerlink" title="右外连接文氏图"></a>右外连接文氏图</h3><p><img src="http://oo77gy3uq.bkt.clouddn.com/14906671586536.jpg" alt=""></p>
<h4 id="执行的sql语句以及执行的查询结果-2"><a href="#执行的sql语句以及执行的查询结果-2" class="headerlink" title="执行的sql语句以及执行的查询结果"></a>执行的sql语句以及执行的查询结果</h4><ul>
<li>执行的sql语句</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">select * from tbl_dept a right join tbl_emp b on a.id=b.deptId;</div></pre></td></tr></table></figure>
<ul>
<li>查询结果<br>  <img src="http://oo77gy3uq.bkt.clouddn.com/14906671715548.png" alt=""></li>
</ul>
<h2 id="左连接"><a href="#左连接" class="headerlink" title="左连接"></a>左连接</h2><h3 id="左连接文氏图"><a href="#左连接文氏图" class="headerlink" title="左连接文氏图"></a>左连接文氏图</h3><p><img src="http://oo77gy3uq.bkt.clouddn.com/14906671817769.png" alt=""></p>
<h4 id="执行的sql语句以及执行的查询结果-3"><a href="#执行的sql语句以及执行的查询结果-3" class="headerlink" title="执行的sql语句以及执行的查询结果"></a>执行的sql语句以及执行的查询结果</h4><ul>
<li>执行的sql语句</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">elect * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;</div></pre></td></tr></table></figure>
<ul>
<li>查询结果</li>
</ul>
<p><img src="http://oo77gy3uq.bkt.clouddn.com/14906671960016.png" alt=""></p>
<h2 id="右连接"><a href="#右连接" class="headerlink" title="右连接"></a>右连接</h2><h3 id="右连接文氏图"><a href="#右连接文氏图" class="headerlink" title="右连接文氏图"></a>右连接文氏图</h3><p><img src="http://oo77gy3uq.bkt.clouddn.com/14906672122367.jpg" alt=""></p>
<h4 id="执行的sql语句以及执行的查询结果-4"><a href="#执行的sql语句以及执行的查询结果-4" class="headerlink" title="执行的sql语句以及执行的查询结果"></a>执行的sql语句以及执行的查询结果</h4><ul>
<li>执行的sql语句</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;</div></pre></td></tr></table></figure>
<ul>
<li>查询结果</li>
</ul>
<p><img src="http://oo77gy3uq.bkt.clouddn.com/14906672303744.png" alt=""></p>
<h2 id="全连接"><a href="#全连接" class="headerlink" title="全连接"></a>全连接</h2><h3 id="全连接文氏图"><a href="#全连接文氏图" class="headerlink" title="全连接文氏图"></a>全连接文氏图</h3><p><img src="http://oo77gy3uq.bkt.clouddn.com/14906672393192.png" alt=""></p>
<h4 id="执行的sql语句以及执行的查询结果-5"><a href="#执行的sql语句以及执行的查询结果-5" class="headerlink" title="执行的sql语句以及执行的查询结果"></a>执行的sql语句以及执行的查询结果</h4><ul>
<li>执行的sql语句</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">select * from tbl_dept a right join tbl_emp b on a.id=b.deptId </div><div class="line">union </div><div class="line">select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;</div></pre></td></tr></table></figure>
<ul>
<li>查询结果<br> <img src="http://oo77gy3uq.bkt.clouddn.com/14906672485581.png" alt=""></li>
</ul>
<h2 id="两张表中都没有出现的数据集"><a href="#两张表中都没有出现的数据集" class="headerlink" title="两张表中都没有出现的数据集"></a>两张表中都没有出现的数据集</h2><h3 id="文氏图"><a href="#文氏图" class="headerlink" title="文氏图"></a>文氏图</h3><p><img src="http://oo77gy3uq.bkt.clouddn.com/14906672571414.jpg" alt=""></p>
<h4 id="执行的sql语句以及执行的查询结果-6"><a href="#执行的sql语句以及执行的查询结果-6" class="headerlink" title="执行的sql语句以及执行的查询结果"></a>执行的sql语句以及执行的查询结果</h4><ul>
<li>执行的sql语句</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null union select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;</div></pre></td></tr></table></figure>
<ul>
<li>查询结果</li>
</ul>
<p><img src="http://oo77gy3uq.bkt.clouddn.com/SouthEast." alt="这里写图片描述"></p>

      
    </div>
    
  </div>
  
    
    <div class="copyright">
        <p><span>本文标题:</span><a href="/2017/04/04/mysql-7-join/">Mysql的七种join</a></p>
        <p><span>文章作者:</span><a href="/" title="回到主页">xiaoyou</a></p>
        <p><span>发布时间:</span>2017-04-04, 17:56:00</p>
        <p><span>最后更新:</span>2017-04-26, 14:06:45</p>
        <p>
            <span>原始链接:</span><a class="post-url" href="/2017/04/04/mysql-7-join/" title="Mysql的七种join">http://yoursite.com/2017/04/04/mysql-7-join/</a>
            <span class="copy-path" data-clipboard-text="原文: http://yoursite.com/2017/04/04/mysql-7-join/　　作者: xiaoyou" title="点击复制文章链接"><i class="fa fa-clipboard"></i></span>
            <script> var clipboard = new Clipboard('.copy-path'); </script>
        </p>
        <p>
            <span>许可协议:</span><i class="fa fa-creative-commons"></i> <a rel="license" href="http://creativecommons.org/licenses/by-nc-sa/4.0/" title="CC BY-NC-SA 4.0 International" target = "_blank">"署名-非商用-相同方式共享 4.0"</a> 转载请保留原文链接及作者。
        </p>
    </div>



    <nav id="article-nav">
        
            <div id="article-nav-newer" class="article-nav-title">
                <a href="/2017/04/04/hello-world/">
                    Hello World
                </a>
            </div>
        
        
            <div id="article-nav-older" class="article-nav-title">
                <a href="/2017/04/10/tomcat-cluster/">
                    Tomcat 集群实现源码级别剖析
                </a>
            </div>
        
    </nav>

  
</article>

    <div id="toc" class="toc-article">
        <strong class="toc-title">文章目录</strong>
        
            <ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#Mysql的七种join"><span class="toc-number">1.</span> <span class="toc-text">Mysql的七种join</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#建表"><span class="toc-number">2.</span> <span class="toc-text">建表</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#文氏图与SQL语句的编写以及查询结果"><span class="toc-number">3.</span> <span class="toc-text">文氏图与SQL语句的编写以及查询结果</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#内连接"><span class="toc-number">3.1.</span> <span class="toc-text">内连接</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#内连接文氏图"><span class="toc-number">3.1.1.</span> <span class="toc-text">内连接文氏图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#执行的sql语句以及执行的查询结果"><span class="toc-number">3.1.1.1.</span> <span class="toc-text">执行的sql语句以及执行的查询结果</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#左外连接"><span class="toc-number">3.2.</span> <span class="toc-text">左外连接</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#左外连接文氏图"><span class="toc-number">3.2.1.</span> <span class="toc-text">左外连接文氏图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#执行的sql语句以及执行的查询结果-1"><span class="toc-number">3.2.1.1.</span> <span class="toc-text">执行的sql语句以及执行的查询结果</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#右外连接"><span class="toc-number">3.3.</span> <span class="toc-text">右外连接</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#右外连接文氏图"><span class="toc-number">3.3.1.</span> <span class="toc-text">右外连接文氏图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#执行的sql语句以及执行的查询结果-2"><span class="toc-number">3.3.1.1.</span> <span class="toc-text">执行的sql语句以及执行的查询结果</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#左连接"><span class="toc-number">3.4.</span> <span class="toc-text">左连接</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#左连接文氏图"><span class="toc-number">3.4.1.</span> <span class="toc-text">左连接文氏图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#执行的sql语句以及执行的查询结果-3"><span class="toc-number">3.4.1.1.</span> <span class="toc-text">执行的sql语句以及执行的查询结果</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#右连接"><span class="toc-number">3.5.</span> <span class="toc-text">右连接</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#右连接文氏图"><span class="toc-number">3.5.1.</span> <span class="toc-text">右连接文氏图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#执行的sql语句以及执行的查询结果-4"><span class="toc-number">3.5.1.1.</span> <span class="toc-text">执行的sql语句以及执行的查询结果</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#全连接"><span class="toc-number">3.6.</span> <span class="toc-text">全连接</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#全连接文氏图"><span class="toc-number">3.6.1.</span> <span class="toc-text">全连接文氏图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#执行的sql语句以及执行的查询结果-5"><span class="toc-number">3.6.1.1.</span> <span class="toc-text">执行的sql语句以及执行的查询结果</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#两张表中都没有出现的数据集"><span class="toc-number">3.7.</span> <span class="toc-text">两张表中都没有出现的数据集</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#文氏图"><span class="toc-number">3.7.1.</span> <span class="toc-text">文氏图</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#执行的sql语句以及执行的查询结果-6"><span class="toc-number">3.7.1.1.</span> <span class="toc-text">执行的sql语句以及执行的查询结果</span></a></li></ol></li></ol></li></ol></li></ol>
        
    </div>
    <style>
        .left-col .switch-btn,
        .left-col .switch-area {
            display: none;
        }
        .toc-level-3 i,
        .toc-level-3 ol {
            display: none !important;
        }
    </style>

    <input type="button" id="tocButton" value="隐藏目录"  title="点击按钮隐藏或者显示文章目录">

    <script>
        yiliaConfig.toc = ["隐藏目录", "显示目录", !!"false"];
    </script>



    
<div class="share">
    
        <div class="bdsharebuttonbox">
            <a href="#" class="fa fa-twitter bds_twi" data-cmd="twi" title="分享到推特"></a>
            <a href="#" class="fa fa-weibo bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a>
            <a href="#" class="fa fa-qq bds_sqq" data-cmd="sqq" title="分享给 QQ 好友"></a>
            <a href="#" class="fa fa-files-o bds_copy" data-cmd="copy" title="复制网址"></a>
            <a href="#" class="fa fa fa-envelope-o bds_mail" data-cmd="mail" title="通过邮件分享"></a>
            <a href="#" class="fa fa-weixin bds_weixin" data-cmd="weixin" title="生成文章二维码"></a>
            <a href="#" class="fa fa-share-alt bds_more" data-cmd="more"></i></a>
        </div>
        <script>
            window._bd_share_config={
                "common":{"bdSnsKey":{},"bdText":"Mysql的七种join　| Lost Youth　","bdMini":"2","bdMiniList":false,"bdPic":"","bdStyle":"0","bdSize":"24"},"share":{}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];
        </script>
    

    
</div>







    




    <div class="scroll" id="post-nav-button">
        
            <a href="/2017/04/04/hello-world/" title="上一篇: Hello World">
                <i class="fa fa-angle-left"></i>
            </a>
        

        <a title="文章列表"><i class="fa fa-bars"></i><i class="fa fa-times"></i></a>

        
            <a href="/2017/04/10/tomcat-cluster/" title="下一篇: Tomcat 集群实现源码级别剖析">
                <i class="fa fa-angle-right"></i>
            </a>
        
    </div>

    <ul class="post-list"><li class="post-list-item"><a class="post-list-link" href="/2017/05/20/distrute-in-action/">分布式服务化系统一致性的“最佳实干“</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/05/12/spring-boot-in-action/">Spring Boot In Action</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/05/04/mysql-youhua/">我必须得告诉大家的MySQL优化原理</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/26/mysql-bizhibimui/">MySql 必知必会</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/26/java-8-in-action/">Java8 In Action</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/26/java-8-in-practice/">Java8学习笔记</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/23/distribute-micro-service-1/">微服务业务开发三个难题－拆分、事务、查询（上）</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/23/distribute-micro-service-2/">微服务业务开发三个难题－拆分、事务、查询（下）</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/22/meituan-id/">Leaf——美团点评分布式ID生成系统</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/10/rocket-in-practice-1/">RocketMQ实战（一）</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/10/head-first-git-branch/">Git由浅入深之分支管理</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/10/tomcat-principle/">Tomcat组成与工作原理</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/10/tomcat-cluster/">Tomcat 集群实现源码级别剖析</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/04/mysql-7-join/">Mysql的七种join</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/04/04/hello-world/">Hello World</a></li></ul>




    <script>
        
    </script>
</div>
      <footer id="footer">
    <div class="outer">
        <div id="footer-info">
            <div class="footer-left">
                <i class="fa fa-copyright"></i> 
                2016-2017 xiaoyou
            </div>
            <div class="footer-right">
                <a href="http://hexo.io/" target="_blank" title="快速、简洁且高效的博客框架">Hexo</a>  Theme <a href="https://github.com/MOxFIVE/hexo-theme-yelee" target="_blank" title="简而不减 Hexo 双栏博客主题  v3.5">Yelee</a> by MOxFIVE <i class="fa fa-heart animated infinite pulse"></i>
            </div>
        </div>
        
            <div class="visit">
                
                    <span id="busuanzi_container_site_pv" style='display:none'>
                        <span id="site-visit" title="本站到访数"><i class="fa fa-user" aria-hidden="true"></i><span id="busuanzi_value_site_uv"></span>
                        </span>
                    </span>
                
                
                    <span>| </span>
                
                
                    <span id="busuanzi_container_page_pv" style='display:none'>
                        <span id="page-visit"  title="本页阅读量"><i class="fa fa-eye animated infinite pulse" aria-hidden="true"></i><span id="busuanzi_value_page_pv"></span>
                        </span>
                    </span>
                
            </div>
        
    </div>
</footer>
    </div>
    
<script data-main="/js/main.js" src="//cdn.bootcss.com/require.js/2.2.0/require.min.js"></script>

    <script>
        $(document).ready(function() {
            var iPad = window.navigator.userAgent.indexOf('iPad');
            if (iPad > -1 || $(".left-col").css("display") === "none") {
                var bgColorList = ["#9db3f4", "#414141", "#e5a859", "#f5dfc6", "#c084a0", "#847e72", "#cd8390", "#996731"];
                var bgColor = Math.ceil(Math.random() * (bgColorList.length - 1));
                $("body").css({"background-color": bgColorList[bgColor], "background-size": "cover"});
            }
            else {
                var backgroundnum = 5;
                var backgroundimg = "url(/background/bg-x.jpg)".replace(/x/gi, Math.ceil(Math.random() * backgroundnum));
                $("body").css({"background": backgroundimg, "background-attachment": "fixed", "background-size": "cover"});
            }
        })
    </script>





<div class="scroll" id="scroll">
    <a href="#" title="返回顶部"><i class="fa fa-arrow-up"></i></a>
    <a href="#comments" onclick="load$hide();" title="查看评论"><i class="fa fa-comments-o"></i></a>
    <a href="#footer" title="转到底部"><i class="fa fa-arrow-down"></i></a>
</div>
<script>
    // Open in New Window
    
        var oOpenInNew = {
            
            
            
            
            
            
             archives: ".archive-article-title", 
             miniArchives: "a.post-list-link", 
            
             friends: "#js-friends a", 
             socail: ".social a" 
        }
        for (var x in oOpenInNew) {
            $(oOpenInNew[x]).attr("target", "_blank");
        }
    
</script>

<script async src="https://dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
</script>
  </div>
</body>
</html>